package de.unimannheim.ki.tests;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import de.unimannheim.ki.databaseentities.MusicObject;
import de.unimannheim.ki.databaseentities.Musicuser;
import de.unimannheim.ki.usermanagement.DatabaseUserDAO;
import weka.core.Instance;
import weka.core.Instances;
import weka.experiment.InstanceQuery;


public class Wekatest2 {
	public static void main(String[] args){
		
		DatabaseUserDAO dao = DatabaseUserDAO.getDAO();
		/*File sfile = new File("sample5.csv");
		PrintWriter pw = null;
		try {
			//pw = new PrintWriter(sfile);
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		 */	
		List<MusicObject> objects = dao.getAllObjects();
		List<Musicuser> user = dao.getAllUsers();
		// first write header line
	
			
		HashMap<Integer, Integer> testmap  = new HashMap<Integer, Integer>(); 
		
		List<MusicObject> testlist = new ArrayList<MusicObject>();
		boolean first = true; 
		
		String line ="create table testdata(";
		for (MusicObject object : objects) {
			//System.out.println(((double) ((double) object.getMusicusers().size())/ ((double)user.size())) +"");
			if (  ((double) ((double) object.getMusicusers().size())/ ((double)user.size())) > 0.00751){
				// consider it
				testmap.put(object.getId(), object.getId());
				testlist.add(object);
				//System.out.println("consider it");
				if(first) {
					line = line + "i"+object.getId() + " VARCHAR(1) ";
				}else {
					line = line+ ",i"+object.getId() +" VARCHAR(1) ";
				}
				first = false;
			}
		}
		
		line = line +") ENGINE=InnoDB;";
		System.out.println(testlist.size());
		String create = line;
		System.out.println(line);
		//pw.print(line +"\n");
		List<String> insertqueries = new ArrayList<String>();
		List<MusicObject> userobjects = null;
		
		first = true;
		for (Musicuser musicuser : user) {
			line ="Insert into testdata values(";
			userobjects = musicuser.getObjects();
			
			if (userobjects.size()== 0) continue;
				first = true;
			for(int i= 0; i < testlist.size(); i++){
				
				if (userobjects.contains(testlist.get(i))){
					if(first) {
						line = line +"'j'";
					}else {
						line = line+ ","+"'j'";
					}
				} else {
					if(first) {
						line = line +"null";
					}else {
						line = line+ ",null";
					}
				}
				first = false;
			}
			insertqueries.add(line+");");
			//pw.print(line +"\n");
		}
		
		System.out.println(insertqueries.get(0));
		//pw.flush();
		//pw.close();
		
		
		
        Connection con = null;

        String url = "jdbc:mysql://aa6bvx41gtnv7k.cwluancabhdk.eu-west-1.rds.amazonaws.com:3306/";
        String db = "ebdb";
        String driver = "com.mysql.jdbc.Driver";
        String tuser = "test";
        String pass = "testtest";
        Statement stmt = null;
        try {
            Class.forName(driver);
            con = DriverManager.getConnection(url + db, tuser, pass);

            System.out.println("jdbc driver for mysql : " + driver);

            System.out.println("Connection url : " + url + db);
            System.out.println("Connection is created...");

            //first create table with the layout ID - Object1 - Object2 .....
            stmt = con.createStatement();
            stmt.executeUpdate(create);
            stmt = con.createStatement();
           
            for (String sql : insertqueries) {
            	 stmt.addBatch(sql);
            	 
			}
            System.out.println("Batch started");
            stmt.executeBatch();
            //TODO put stuff into db
            con.close();
            System.out.println("Connection is closed...");
        } catch (Exception e) {
            System.out.println(e);
        }
		
	/*
		
		
		boolean first = true; 
		
		List<MusicObject> userobjects = null;
		int biggestsetsize = 0;
		for (Musicuser musicuser : user) {
			if ( musicuser.getObjects().size() > biggestsetsize) {
				biggestsetsize = musicuser.getObjects().size();
				
			}
		}
		
		System.out.println("biggest size;" + biggestsetsize);
		
		String line ="";
		
		first = true;
		for (int i = 0; i < biggestsetsize; i ++){
			if(first) {
				line = line + "itemno"+i;
			}else {
				line = line+ ","+"itemno"+i;
			}
			first = false;
		}
		
		pw.print(line +"\n");
		
		first = true;
		for (Musicuser musicuser : user) {
			line ="";
			userobjects = musicuser.getObjects();
			
			if (userobjects.size()== 0) continue;
				first = true;
			for(int i= 0; i < biggestsetsize; i++){
				
				if(i < userobjects.size()){
					if(first) {
						line = line +userobjects.get(i).getId();
					}else {
						line = line+ ","+userobjects.get(i).getId();
					}
					
				}else {
					if(first) {
						line = line +"?";
					}else {
						line = line+ ",?";
					}
				}
				first = false;
			}
			pw.print(line +"\n");
		}
		pw.flush();
		pw.close();
		
		/*
		for (Musicuser musicuser : user) {
			theline="";
			first = true;
			userobjects = musicuser.getObjects();
			//loop over all object 
			 objects = dao.getAllObjects();
			for (MusicObject musicObject : objects) {	
				if(! first) {
					if(userobjects.contains(musicObject)){
						theline = theline + ", " + "1";
					}else {
						theline = theline + ", " + "0";
					}
				} else {
					first = false;
					if(userobjects.contains(musicObject)){
						theline = theline + "1";
					}else {
						theline = theline + "0";
					}
				}
			}
			if (!theline.equals("")){
				
				pw.println(theline);
			}
		}
		*/
		
		 InstanceQuery query =  null;
		/*try {
			query = new InstanceQuery();
			 query.setUsername("test");
			 query.setPassword("testtest");
			 query.setQuery("select * from MusicObject m inner join likes l on m.ID = l.ObjectId inner join MUSICUSER mu ");
			 
			 Instances data = query.retrieveInstances();
			 
			Instance bla = new Instance(6);
			 
			System.out.println( data.numInstances());
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		*/
		
	}
}
